package br.com.pheat.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.pheat.beans.Alimento;
import br.com.pheat.beans.Refeicao;
import br.com.pheat.beans.RefeicaoAlimento;
import br.com.pheat.factory.ConnectionFactory;

public class RefeicaoDAO {

	public static void inserirRefeicao(Refeicao refeicao, Integer codigoUsuario) throws SQLException{
		
		Connection conn = ConnectionFactory.getConnection();
		String sql = "insert into refeicao(codigo_usuario,data,tipo_refeicao) values ("+codigoUsuario+",'"+refeicao.getData()+"','"+refeicao.getTipo_refeicao()+"');";
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = stmt.getGeneratedKeys();
		rs.next();
		Integer codigo = rs.getInt(1);
		sql = "insert into refeicao_alimento(codigo_refeicao,codigo_alimento,porcao) values(?,?,?);";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, codigo);
		for(RefeicaoAlimento refeicaoAlimento:refeicao.getRefeicaoAlimentos())
		{
			pstmt.setInt(2, refeicaoAlimento.getAlimento().getCodigo());
			pstmt.setDouble(3, refeicaoAlimento.getPorcao());
			pstmt.executeUpdate();
		}
		rs.close();
		stmt.close();
		conn.close();
	}
	
public static List<Refeicao> getRefeicao(String dataRefeicao, Integer codigoUsuario) throws SQLException{
		
		Connection conn = ConnectionFactory.getConnection();
		List<Refeicao> refeicoes = new ArrayList<Refeicao>();
		
		String sql = "SELECT codigo,data, tipo_refeicao, (SELECT SUM(a.calorias * r.porcao) FROM alimento a INNER JOIN refeicao_alimento r ON r.codigo_alimento = a.codigo WHERE r.codigo_refeicao = refeicao.codigo) as calorias_total FROM refeicao WHERE data= date(?) AND codigo_usuario=?;";
		
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, dataRefeicao);
		stmt.setInt(2,codigoUsuario);
		
		ResultSet rs = stmt.executeQuery();

		while(rs.next()) {
			Refeicao refeicao = new Refeicao();
			refeicao.setCodigo(rs.getInt("codigo"));
			refeicao.setData(rs.getString("data"));
			refeicao.setTipo_refeicao(rs.getString("tipo_refeicao"));
			refeicao.setTotalCalorias(rs.getDouble("calorias_total"));
			refeicao.setRefeicaoAlimentos(AlimentoDAO.getRefeicaoAlimento(rs.getInt("codigo")));

			refeicoes.add(refeicao);
		}
		
		return refeicoes;
	}
	
	public static String getConsumoPorDiaSemana(int codigo_usuario) throws SQLException{
		Connection conn = ConnectionFactory.getConnection();

		String sql = "SELECT to_char(r.data, 'D') as dia_semana, SUM(ra.porcao * a.calorias)/COUNT(distinct(r.data)) as media ";
		sql += "FROM refeicao r ";
		sql += "INNER JOIN refeicao_alimento ra ON r.codigo = ra.codigo_refeicao ";
		sql += "INNER JOIN alimento a ON ra.codigo_alimento = a.codigo ";
		sql += "WHERE r.codigo_usuario = ?";
		sql += "GROUP BY dia_semana ORDER BY dia_semana ASC";

		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, codigo_usuario);

		ResultSet rs = stmt.executeQuery();
		
		float dom = 0;
		float seg = 0;
		float ter = 0;
		float qua = 0;
		float qui = 0;
		float sex = 0;
		float sab = 0;

		while(rs.next()) {
			switch(rs.getInt("dia_semana")) {
				case 1:
					dom = rs.getFloat("media");
					break;
				case 2:
					seg = rs.getFloat("media");
					break;
				case 3:
					ter = rs.getFloat("media");
					break;
				case 4:
					qua = rs.getFloat("media");
					break;
				case 5:
					qui = rs.getFloat("media");
					break;
				case 6:
					sex = rs.getFloat("media");
					break;
				case 7:
					sab = rs.getFloat("media");
					break;
			}
		}

		String retorno = "{dom: " + dom + ", seg: " + seg + ", ter: " + ter + ", qua: " + qua + ", qui: " + qui + ", sex: " + sex + ", sab: " + sab + "}";
		return retorno;
	}
}